/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import java.sql.*;
import java.util.Properties;
/**
 *
 * @author tran quoc thinh
 */
public class MySQLDataAccessHelper {
    private Connection connect;
    
    public void Open()
    {
        try{
            Driver d = new org.gjt.mm.mysql.Driver();
            DriverManager.registerDriver(d);
            String conString = "jdbc:mysql://localhost:3306/chieuphimonline";
            Properties info = new Properties();
            info.setProperty("characterEncoding", "utf-8");
            info.setProperty("user", "root");
            connect = DriverManager.getConnection(conString, info);            
        }catch(Exception ex){
            System.out.println(ex.getMessage());
        }
    }
    
    public void Close()
    {
        try{
            connect.close();
        }catch(Exception ex){
            System.out.println(ex.getMessage());
        }
    }
    
    public ResultSet excecuteQuery(String sql)
    {
        ResultSet rs = null;
        try{
            Statement st = connect.createStatement();
            rs = st.executeQuery(sql);
            
        }catch(Exception ex){
            System.out.println(ex.getMessage());
        }
        return rs;
    }
    
    /**
     * Hiển thị sách theo thể loại
     * @param maTheLoai
     * @return 
     */
//    public ResultSet excecuteQueryCallAble(int maTheLoai)
//    {
//        ResultSet rs = null;
//        try{
//            CallableStatement cst = connect.prepareCall("{call spDSSach(?)}");
//            cst.setInt(1, maTheLoai);
//            cst.execute();
//            rs = cst.getResultSet();            
//        }catch(Exception ex){
//            System.out.println(ex.getMessage());
//        }
//        return rs;
//    }
    
    /**
     * Tìm sách theo tên và giá bán
     * @param maTheLoai
     * @return 
     */
//    public ResultSet excecuteQueryCallAbleFind(String tenSach, float giaBan)
//    {
//        ResultSet rs = null;
//        try{
//            CallableStatement cst = connect.prepareCall("{call spTimSach(?,?)}");
//            cst.setString(1, tenSach);
//            cst.setFloat(2, giaBan);
//            cst.execute();
//            rs = cst.getResultSet();            
//        }catch(Exception ex){
//            System.out.println(ex.getMessage());
//        }
//        return rs;
//    }
//    
    public int excecuteUpdate(String sql){
        int n = -1;
        try{
            Statement st = connect.createStatement();
            n = st.executeUpdate(sql);
        }catch(Exception ex){
            System.out.println(ex.getMessage());
        }
        return n;
    }
    
    public int excecuteUpdateGetKey(String sql){
        int maHoaDon = -1;
        try{
            Statement st = connect.createStatement();
            int n = st.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
            if(n>0)
            {
                ResultSet resulttemp = st.getGeneratedKeys();                            
                while(resulttemp.next())
                {
                    maHoaDon = resulttemp.getInt(1);                                
                }                    
            }
        }catch(Exception ex){
            System.out.println(ex.getMessage());
        }
        return maHoaDon;
    }
}
